package de.hska.util;

import static de.hska.util.LogUtil.begin;
import static de.hska.util.LogUtil.end;
import static org.dbunit.operation.DatabaseOperation.CLEAN_INSERT;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Properties;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dbunit.DatabaseUnitException;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;

public class DbUtil {
	private static final String PROPSFILE = "db.properties";
	private static final String SCHEMA;
	private static final String URL;
	private static final String USERNAME;
	private static final String PASSWORD;
	private static final String XML_FLAT_DATASET;
	
	private static Connection conn;
	
	private static final String KUNDE_ID_ALL_STR = "SELECT k_id FROM kunde ORDER BY k_id ASC";
	private static PreparedStatement kundeIdAllPstmt;
	
	private static final String KUNDE_NACHNAME_ALL_STR = "SELECT nachname FROM kunde ORDER BY nachname ASC";
	private static PreparedStatement kundeNachnameAllPstmt;
	
	private static final Log LOG = LogFactory.getLog(DbUtil.class);
	private static final boolean DEBUG = LOG.isDebugEnabled();
	
	static {
		// Properties-Datei einlesen
		final ClassLoader classLoader = DbUtil.class.getClassLoader();
		final InputStream inputStream = classLoader.getResourceAsStream(PROPSFILE);
		Properties props = new Properties();
		try {
			props.load(inputStream);
		}
		catch (IOException e) {
			e.printStackTrace();
		}
		finally {
			try {
				inputStream.close();
			}
			catch (IOException e) {
				e.printStackTrace();
			}
		}
		
		// Properties fuer DB-Connection einlesen
		URL = props.getProperty("url");
		SCHEMA = props.getProperty("schema");
		USERNAME = props.getProperty("username");
		PASSWORD = props.getProperty("password");
		XML_FLAT_DATASET = props.getProperty("xmlDataset");
		
		final String driver = props.getProperty("driver");
		try {
			Class.forName(driver);
		}
		catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 */
	public static void open() throws SQLException {
		if (DEBUG) begin(LOG);
		
		// DB-Connection erzeugen
		if (conn == null)
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		
		// Prepared Statements erzeugen
		kundeIdAllPstmt = conn.prepareStatement(KUNDE_ID_ALL_STR);
		kundeNachnameAllPstmt = conn.prepareStatement(KUNDE_NACHNAME_ALL_STR);
		
		if (DEBUG) end(LOG);
	}
	
	/**
	 */
	public static void close() throws SQLException {
		if (DEBUG) begin(LOG);
		
		try {
			kundeIdAllPstmt.close();
			kundeNachnameAllPstmt.close();
			
			conn.close();
		}
		finally {
			kundeIdAllPstmt = null;
			kundeNachnameAllPstmt = null;
			conn = null;
		}
		
		if (DEBUG) end(LOG);
	}
	
	/**
	 */
	public static Collection<Long> getAllKundeId() throws SQLException {
		if (DEBUG) begin(LOG);
		
		final ResultSet rs = kundeIdAllPstmt.executeQuery();		
		final Collection<Long> ids = new ArrayList<Long>();
		try {
			while (rs.next()) {
				final Long id = rs.getLong("k_id");
				ids.add(id);
			}
		}
		finally {
			rs.close();
		}
		
		if (DEBUG) end(LOG, ids);
		return ids;
	}

	
	/**
	 */
	public static Collection<String> getAllKundeNachname() throws SQLException {
		if (DEBUG) begin(LOG);
		
		final ResultSet rs = kundeNachnameAllPstmt.executeQuery();		
		final Collection<String> nachnamen = new ArrayList<String>();
		try {
			while (rs.next()) {
				final String nachname = rs.getString("nachname");
				nachnamen.add(nachname);
			}
		}
		finally {
			rs.close();
		}
		
		if (DEBUG) end(LOG, nachnamen);
		return nachnamen;
	}
	
	
	/**
	 */
	public static void reload() throws FileNotFoundException, IOException, DatabaseUnitException, SQLException {
		if (DEBUG) begin(LOG, URL, USERNAME, PASSWORD, SCHEMA, XML_FLAT_DATASET);
		
		final Connection jdbcConn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		final IDatabaseConnection dbunitConn = new DatabaseConnection(jdbcConn, SCHEMA);
		
		final InputStream inputStream = new BufferedInputStream(new FileInputStream(XML_FLAT_DATASET));
		final IDataSet xmlDataset = new FlatXmlDataSet(inputStream);
		inputStream.close();
		
		CLEAN_INSERT.execute(dbunitConn, xmlDataset);
		
		dbunitConn.close();
		jdbcConn.close();
		
		if (DEBUG) end(LOG);
	}
}
